import pymysql

class getMysqlData():
    def __init__(self):
        # mysql数据库
        db_config = {
            'host': '8.141.23.4',  # 数据库服务器地址
            'port': 3306,  # 数据库端口
            'user': 'remote_user',  # 数据库用户名
            'password': 'mjquPH3kGKFYsGykH&x',  # 数据库密码
            'db': 'business_data'  # 数据库名
        }
        self.connection = pymysql.connect(**db_config)

    # 获取网页原文
    def getWebSiteCrawlContent(self, website):
        with self.connection.cursor() as cursor:
            # 先尝试匹配官方网址
            sql = 'SELECT crawl_content FROM ent_website_info WHERE offcial_website="%s" AND LENGTH(crawl_content)>10 ORDER BY update_time DESC LIMIT 1'%(website)
            cursor.execute(sql)
            result = cursor.fetchone()
            if result:
                return result[0]
            # # 如果没有匹配到官方网址，再尝试匹配其他网址
            # print("（通过other_website查询）")
            # sql = 'SELECT crawl_content FROM ent_website_info WHERE other_website LIKE "%s"'%('%' + website + '%')
            # cursor.execute(sql)
            # result = cursor.fetchone()
            # if result:
            #     return result[0]
            return ""

    # 爬取多产品结果汇总
    def getSpiderInfo(self, companyName):
        # 爬取解析表获取所有结果
        website = ""
        webText = ""
        sourceCount = 0
        with self.connection.cursor() as cursor:
            sql = ("""
            SELECT source_type,website,major_product 
            FROM company_result_info_info 
            WHERE company_name = %s
            ORDER BY source_type ASC
            """)
            cursor.execute(sql, (companyName,))
            results = cursor.fetchall()
        # 多产品结果汇总
        for item in results:
           if item[0] == 0: # 谷歌搜索到的官网
               if item[2] and (item[2]!="未提及"):
                   webText += "公司产品：%s\n"%item[2]
               if "sme_service.com" not in item[1]: # 中小企业网产品错误严重，不使用
                   r = self.getWebSiteCrawlContent(item[1])  # 需要网址原文增强解析
                   webText += r
                   if r:
                       website = item[1]
                   # 存在一个历史错误，尝试找到正确的，没找到对应网址时尝试修复
                   if (r=="") and (item[1][-19:]=="//main.company_name"):
                       r = self.getWebSiteCrawlContent(item[1][:-18])  # 需要网址原文增强解析
                       webText += r
                       if r:
                           website = item[1][:-18] # 返回修正的网址
               sourceCount += 1
           if item[0] == 1: # 中小企业网产品错误严重，不使用
               continue
           if item[0] == 2: # 1688来源
               if item[2] and (item[2]!="未提及"):
                   webText += "公司产品：%s\n"%item[2]
               sourceCount += 1
        return webText, website, sourceCount





if __name__ == "__main__":
    companyName = "万向集团公司"
    mysqlData = getMysqlData()

    # # 网页原文
    # webText = mysqlData.getWebSiteCrawlContent("http://www.wanxiang.com.cn/")
    # print(webText)

    # 多渠道爬取产品结果汇总
    webText, webSite, sourceCount = mysqlData.getSpiderInfo(companyName)
    print(webText)
    print(webSite)
    print(sourceCount)
